/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.xinxi.dms.service;

import com.xinxi.dms.dao.BaseDao;
import com.xinxi.dms.dao.CheckinDao;
import com.xinxi.dms.dao.RoomDao;
import com.xinxi.dms.dao.StudentDao;
import com.xinxi.dms.po.Room;
import com.xinxi.dms.po.Stay;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author zhigu
 */
public class ChangeServiceIMPL implements IChangeService{
    CheckinDao changeDao = new CheckinDao();
    RoomDao roomDao = new RoomDao();
    StudentDao studentDao = new StudentDao();
    @Override
    public List<Stay> findAll_25(String unitno,String roomno) {
        String sql="select s.stuid,s.stuname,s.stusex,s.stuschool,r.buildingno,r.unitno,r.roomno,s.state,r.roomid " +
                   "from student as s,room as r " +
                   "where s.buildingno = r.buildingno " +
                   "and s.unitno = r.unitno " +
                   "and s.roomno = r.roomno " +
                   "and r.unitno = ? and r.roomno = ? and r.buildingno = 25 and s.state = '已入住'";
        Object[] params={unitno,roomno};
        return changeDao.query(sql,Stay.class, params);
    }

    @Override
    public int update(Stay stay) {
        String sql_1="insert into stay(stuid,roomid,changetime,changereason) "//插入stay表一条信息1
                + "values(?,?,now(),?)";
        Object[] params_1={stay.getStuid(),stay.getRoomid(),stay.getChangereason()};
        String sql_2="update student set buildingno = ?,unitno=?,roomno=? where stuid=?";//修改选中信息的楼房信息2
        Object[] params_2={stay.getBuildingno(),stay.getUnitno(),stay.getRoomno(),stay.getStuid()};
        String sql_3="update room set checknumber=checknumber-1 where roomid =?"; //扣除原有宿舍已住人数3
        Object[] params_3={stay.getRoomid()};
        String sql_4="update room set checknumber=checknumber+1 where buildingno = ? and unitno=? and roomno=?"; //增加现有宿舍已住人数4
        Object[] params_4={stay.getBuildingno(),stay.getUnitno(),stay.getRoomno()};
        String sql_5="update room set checknumber=checknumber-1,state=? where roomid =?";//如果扣除宿舍人数时人数为零  关闭宿舍5
        Object[] params_5={"未开放",stay.getRoomid()};
        String sql_6="update room set checknumber=checknumber+1,state=? where buildingno = ? and unitno=? and roomno=?";//如果增加宿舍人数时人数为零  开放宿舍6
        Object[] params_6={"开放",stay.getBuildingno(),stay.getUnitno(),stay.getRoomno()};
        String sql_7 = "select buildingno,unitno,roomno,state,checknumber,remarks from room where roomid = ?";//7
        Object[] params_7={stay.getRoomid()};
        int result = 0;//1为成功，2为失败，3为房间已满
        Connection conn= new BaseDao().getConnection();
        try {        
            conn.setAutoCommit(false);
            Room room = roomDao.get(sql_7, Room.class, params_7);
            if (room.getState().equals("未开放") || room.getState() == "未开放") {
                roomDao.update(sql_6, params_6);//如果增加宿舍人数时人数为零  开放宿舍
                if (room.getChecknumber().equals(1)) {
                    roomDao.update(sql_5, params_5);//如果扣除宿舍人数时人数为零  关闭宿舍
                } else {
                    roomDao.update(sql_3, params_3);//扣除原有宿舍已住人数
                }
                result = 1;
                studentDao.update(sql_2, params_2);
                roomDao.update(conn, sql_1, params_1);
            } else if (room.getChecknumber().equals(room.getRemarks())) {
                result = 3;
            } 
             else if (room.getBuildingno().equals(stay.getBuildingno())&&room.getUnitno().equals(stay.getUnitno())&room.getRoomno().equals(stay.getRoomno())) {
                result = 4;
            }else if(room.getState().equals("开放") || room.getState() == "开放"){
                roomDao.update(sql_4, params_4);//增加现有宿舍已住人数
                if (room.getChecknumber().equals(1)) {
                    roomDao.update(sql_5, params_5);//如果扣除宿舍人数时人数为零  关闭宿舍
                } else {
                    roomDao.update(sql_3, params_3);//扣除原有宿舍已住人数
                }
                result = 1;
                studentDao.update(sql_2, params_2);
                roomDao.update(conn, sql_1, params_1);
            }else
                result = 2;
            conn.commit();
        } catch (SQLException ex) {
            try {
                //事务回滚
                result =2;
                conn.rollback();
                //事务回滚
            } catch (SQLException ex1) {
                Logger.getLogger(CheckinServiceIMPL.class.getName()).log(Level.SEVERE, null, ex1);
            }
            Logger.getLogger(ChangeServiceIMPL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    @Override
    public Room test(Room room) {
        String sql="select remarks,checknumber,roomid from room where buildingno = ? and unitno = ? and roomno = ?";
        Object[] params={room.getBuildingno(),room.getUnitno(),room.getRoomno()};
        return roomDao.get(sql, Room.class, params);
    }
    @Override
    public List<Stay> findAll_23(String unitno,String roomno) {
        String sql="select s.stuid,s.stuname,s.stusex,s.stuschool,r.buildingno,r.unitno,r.roomno,s.state,r.roomid " +
                   "from student as s,room as r " +
                   "where s.buildingno = r.buildingno " +
                   "and s.unitno = r.unitno " +
                   "and s.roomno = r.roomno " +
                   "and r.unitno = ? and r.roomno = ? and r.buildingno = 23 and s.state = '已入住'";
        Object[] params={unitno,roomno};
        return changeDao.query(sql,Stay.class, params);
    }
    
}
